package com.suray.wcs.service.util;

import ch.qos.logback.core.db.DriverManagerConnectionSource;
import com.suray.wcs.service.resmap.WriteMap;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * @author dell
 * @version： task.java v 1.0, 2020年01月18日 12:47
 * @Description 数据库操作
 **/
@Service
public class BackUpDataBaseManager {

    private static final Logger log = LoggerFactory.getLogger(BackUpDataBaseManager.class);

    @Value("${spring.datasource.driver-class-name}")
    private String driverClassName;
    @Value("${spring.datasource.url}")
    private String url;
    @Value("${spring.datasource.username}")
    private String userName;
    @Value("${spring.datasource.password}")
    private String password;

    /**
     * 获取数据库名
     */
    public String getMysqlDataBaseName() {
        return url.substring(url.indexOf("3306"), url.indexOf("?")).replaceAll("/", "").replaceAll("3306", "");
    }

    /**
     * 获取主机地址
     */
    private String getMysqlHost() {
        return url.substring(url.indexOf("mysql"), url.indexOf("3306")).replace(":", "").replace("//", "").replace("mysql", "");
    }

    /**
     * 获取数据库名
     */
    public String getSqlserverDataBaseName() {
        return url.substring(url.indexOf("=") + 1);
    }

    /**
     * 获取主机地址
     */
    private String getSqlserverHost() {
        return url.substring(url.indexOf("sqlserver"), url.indexOf("1433")).replace(":", "").replace("//", "").replace("mysql", "");
    }

    /**
     * 导出 sql 并返回相关信息
     */
    public void exportSql(String time) {
        // 指定导出的 sql 存放的文件夹
        String paperFile = System.getProperty("user.dir") + File.separator + "DB" + File.separator;
        StringBuilder sb = new StringBuilder();
        if (driverClassName.contains("mysql")) {
            String fileName = paperFile + getMysqlDataBaseName() + ".sql";
            try {
                WriteMap.createPaper(fileName);
            } catch (IOException e) {
                e.printStackTrace();
            }
            // 拼接备份命令
            sb.append(" c:\\mysqldump")
                    .append(" -h ").append(getMysqlHost())
                    .append(" -u ").append(userName)
                    .append(" -p").append(password)
                    .append(" --databases ").append(getMysqlDataBaseName());
            //如果要选择部分表备份,可以通过--tables参数
            String[] selectTables = {"sys_dict_data","sys_dict_type","sys_wms_url","wcs_menu", "wcs_connect_config", "wcs_assign_config", "wcs_node", "wcs_rgv", "wcs_plc", "wcs_plcdevice",
                    "wcs_port", "wcs_programme", "wcs_warehouse"};
            sb.append(" --tables");
            for (String selectTable : selectTables) {
                sb.append(" ").append(selectTable);
            }

            sb.append(" >");
            sb.append(fileName);
            log.info("cmd命令为：" + sb.toString());
            try {
                Runtime runtime = Runtime.getRuntime();
                //执行备份命令
                Process exec = runtime.exec("cmd /c" + sb.toString());
                if (exec.waitFor() == 0) {
                    log.info("数据库备份成功，保存路径：" + fileName);
                } else {
                    log.error("数据库备份失败，process.waitFor() = " + exec.waitFor());
                }
            } catch (IOException e) {
                log.error("备份 数据库 出现 IO异常 ", e);
            } catch (Exception e) {
                log.error("备份 数据库 出现 其他异常 ", e);
            }
        } else if (driverClassName.contains("sqlserver")) {
            String sqlserverFileName = paperFile + getSqlserverDataBaseName() + ".bak";
            try {
                WriteMap.createPaper(sqlserverFileName);
            } catch (IOException e) {
                e.printStackTrace();
            }
            sb.append("BACKUP DATABASE[").append(getSqlserverDataBaseName())
                    .append("] to disk = '").append(sqlserverFileName).append("'");
            Connection connection = null;
            try {
                connection = DriverManager.getConnection(url, userName, password);
                PreparedStatement preparedStatement = connection.prepareStatement(sb.toString());
                preparedStatement.execute();
                preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}
